/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package proyecto1.Manager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;
import proyecto1.GestionDB;
import proyecto1.TO.ReunionTO;

/**
 *
 * @author alex
 */
public class ReunionMannager {
    
      GestionDB cnn = new GestionDB();

    public List<ReunionTO> listar() throws Exception {
        ArrayList data = new ArrayList();
        String sql = "SELECT * FROM reunion";

        cnn.conectar();
        ResultSet rs = cnn.ejecutarConsulta(sql);//Consulta a la base de datos y lo almacena en "rs"
        while (rs.next()) {

            ReunionTO t = new ReunionTO();

            t.setID_reunion(rs.getString("ID_reunion"));
            t.setFecha_reunion(rs.getString("fecha_reunion"));
            t.setHora_reunion(rs.getString("hora_reunion"));
            t.setMotivo_reunion(rs.getString("Motivo_reunion"));
            t.setMulta(rs.getString("multa"));
            data.add(t);

        }
        rs.close();
        cnn.desconectar();
        return data;
    }

    public void Agregar(ReunionTO data) throws Exception {

        String sql = "INSERT INTO reunion(ID_reunion,fecha_reunion,hora_reunion,Motivo_reunion,multa) VALUES (?,?,?,?,?)";
        Connection c = null;
        try {
            c = cnn.conectar();
            c.setAutoCommit(false);
            PreparedStatement ps = c.prepareStatement(sql);
            ps.setString(1, data.getID_reunion());
            ps.setString(2, data.getFecha_reunion());
            ps.setString(3, data.getHora_reunion());
            ps.setString(4, data.getMotivo_reunion());
            ps.setString(5, data.getMulta());
            ps.executeUpdate();
            c.commit();
            ps.close();

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.getMessage());
            c.rollback();
        } finally {
            cnn.desconectar();
        }
    }

    public ReunionTO buscaPorid(Object id) throws Exception {
        ReunionTO t = null;
        String sql = "SELECT * FROM reunion WHERE ID_reunion=" + id;
        //id_persona,cod_pe,nom_pe,dni_pe,dir_pe,cel_pe,ema_pe,fecnac_pe
        try {
            cnn.conectar();
            ResultSet rs = cnn.ejecutarConsulta(sql);
            if (rs.next()) {
                t = new ReunionTO();
                t.setID_reunion(rs.getString("ID_reunion"));
                t.setFecha_reunion(rs.getString("fecha_reunion"));
                t.setHora_reunion(rs.getString("hora_reunion"));
                t.setMotivo_reunion(rs.getString("Motivo reunion"));
                t.setMulta(rs.getString("multa"));
                
            }
            rs.close();
            cnn.desconectar();

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.getMessage());
        }

        return t;
    }

//________________________editar 
    public void Editar(ReunionTO data) throws Exception {
        String sql = "UPDATE reunion SET fecha_reunion=?,hora_reunion=?,Motivo_reunion=?,multa=? WHERE ID_reunion=?";
        Connection c = null;
        //id_persona,cod_pe,nom_pe,dni_pe,dir_pe,cel_pe,ema_pe,fecnac_pe
        try {
            c = cnn.conectar();
            c.setAutoCommit(false);
            try (PreparedStatement ps = c.prepareStatement(sql)) {
                ps.setString(5, data.getID_reunion());
                ps.setString(1, data.getFecha_reunion());
                ps.setString(2, data.getHora_reunion());
                ps.setString(3, data.getMotivo_reunion());
                ps.setString(4, data.getMulta());
                ps.executeUpdate();
                c.commit();
            }

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.getMessage());
            c.rollback();
        } finally {
            cnn.desconectar();
        }

    }

    //----------------------------------Eliminar
    public void eliminar(String id) throws Exception {
        try {
            Connection c = cnn.conectar();
            String sql = "DELETE FROM reunion WHERE ID_reunion=?";
            PreparedStatement ps = c.prepareStatement(sql);
            ps.setString(1, id);
            ps.executeUpdate();
            ps.close();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.getMessage());
        } finally {
            cnn.desconectar();
        }

    }
    
    public List<ReunionTO> buscaPorDescripcion(Object des)throws Exception{
   ArrayList data = new ArrayList();
        ReunionTO t=null;
   String sql="SELECT * FROM reunion WHERE Motivo_reunion LIKE '"+des+"%'";
       try {
       cnn.conectar();
       ResultSet rs=cnn.ejecutarConsulta(sql);
       while(rs.next()){
       t=new ReunionTO();
            t.setID_reunion(rs.getString("ID_reunion"));
            t.setFecha_reunion(rs.getString("fecha_reunion"));
            t.setHora_reunion(rs.getString("hora_reunion"));
            t.setMotivo_reunion(rs.getString("Motivo_reunion"));
            t.setMulta(rs.getString("multa"));
            data.add(t);
       }
           rs.close();
           cnn.desconectar();
           
       } catch (Exception e) {
       JOptionPane.showMessageDialog(null, e.getMessage());
       }
   
   return data;
   }
    
    
}
